package com.ll.mybatis;

import com.ll.mybatis.dao.EmployeeMapper;
import com.ll.mybatis.entity.Employee;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.jupiter.api.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 刘龙
 * @version 1.0
 */
public class MybatisXMLCRUD {
    public SqlSessionFactory getsqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    /*
    * mybatis允许添加,删除,修改直接定义以下类型返回值
    *   Integer,Long,Boolean
    * */

    /**
     * 添加
     */
    @Test
    public void add() throws IOException {
        /**
         *  getsqlSessionFactory().openSession(); ==> 手动提交
         *  getsqlSessionFactory().openSession(true); ==> 自动提交
         */
        // 获取的SqlSession不会自定提交
        SqlSession sqlSession = getsqlSessionFactory().openSession();

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

            Employee employee = new Employee(null, "jerry", "0", "jerry@163.com");
            mapper.addEmp(employee);

            // 获取添加后,生成的主键
            System.out.println(employee.getId());
            // 手动提交
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

    /**
     * 修改
     */
    @Test
    public void updateEmp() throws IOException {
        // 获取的SqlSession不会自定提交
        SqlSession sqlSession = getsqlSessionFactory().openSession();

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

            Employee employee = new Employee(1, "jerry", "1", "jerry@163.com");
            mapper.updateEmp(employee);
            // 手动提交
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

    /**
     * 查询
     */
    @Test
    public void getEmp() throws IOException {
        // 获取的SqlSession不会自定提交
        SqlSession sqlSession = getsqlSessionFactory().openSession();

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

            Employee employeeById = mapper.getEmployeeById(1);
            System.out.println(employeeById);
            // 手动提交
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

    /**
     * 查询,查询参数是多个
     */
    @Test
    public void getEmp2() throws IOException {
        // 获取的SqlSession不会自定提交
        SqlSession sqlSession = getsqlSessionFactory().openSession();

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

            Employee employee = mapper.getEmployeeByIdAndLastNmae(1, "jerry");
            System.out.println(employee);
            // 手动提交
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

    /**
     * 查询,查询参数是多个
     */
    @Test
    public void getEmp3() throws IOException {
        // 获取的SqlSession不会自定提交
        SqlSession sqlSession = getsqlSessionFactory().openSession();

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

            Map<String, Object> map = new HashMap<>();
            map.put("id", 1);
            map.put("lastName", "jerry");

            Employee employee = mapper.getEmployeeByMap(map);
            System.out.println(employee);
            // 手动提交
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

    /**
     * 查询,条件是多个个条件 涉及${}
     */
    @Test
    public void getEmp4() throws IOException {
        // 获取的SqlSession不会自定提交
        SqlSession sqlSession = getsqlSessionFactory().openSession();

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

            Map<String, Object> map = new HashMap<>();
            map.put("id", 1);
            map.put("lastName", "jerry");
            map.put("tableName", "tbl_employee");

            Employee employee = mapper.getEmployeeByMap2(map);
            System.out.println(employee);
            // 手动提交
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

    /**
     * 查询. 返回集合
     *      <select id="getEmpsByLastNameLike" resultType="com.ll.mybatis.entity.Employee">
     *         SELECT *
     *         FROM tbl_employee
     *         WHERE last_name LIKE #{lastName}
     *     </select>
     * @throws IOException
     */
    @Test
    public void getEmp5() throws IOException {
        // 获取的SqlSession不会自定提交
        SqlSession sqlSession = getsqlSessionFactory().openSession();

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            List<Employee> employees = mapper.getEmpsByLastNameLike("%e%");
            for (Employee employee : employees) {
                System.out.println(employee);
            }
        } finally {
            sqlSession.close();
        }
    }

    /**
     * 查询,返回map
     *     <select id="getEmpByIdReturnMap" resultType="map">
     *         select *
     *         from tbl_employee
     *         where id = #{id}
     *     </select>
     * @throws IOException
     */
    @Test
    public void getEmp6() throws IOException {
        // 获取的SqlSession不会自定提交
        SqlSession sqlSession = getsqlSessionFactory().openSession();

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            Map<String, Object> empMaps = mapper.getEmpByIdReturnMap(1);
            System.out.println(empMaps);
        } finally {
            sqlSession.close();
        }
    }

    /**
     * 查询.多条记录封装一个map, Key就是列名, 值是javaBean
     *
     *     @MapKey("id")
     *     public Map<Integer, Employee> getEmpsByLastNameLikeReturnMap(String lastName);
     *
     *     <select id="getEmpsByLastNameLikeReturnMap" resultType="com.ll.mybatis.entity.Employee">
     *         SELECT *
     *         FROM tbl_employee
     *         WHERE last_name LIKE #{lastName}
     *     </select>
     * @throws IOException
     */
    @Test
    public void getEmp7() throws IOException {
        // 获取的SqlSession不会自定提交
        SqlSession sqlSession = getsqlSessionFactory().openSession();

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            Map<Integer, Employee> employeeMap = mapper.getEmpsByLastNameLikeReturnMap("%j%");
            // {1=Employee{id=1, lastname='jerry', gender='1', email='jerry@163.com'}, 3=Employee{id=3, lastname='jerry', gender='0', email='jerry@163.com'}, ...
            System.out.println(employeeMap);
        } finally {
            sqlSession.close();
        }
    }


    /**
     * 删除
     */
    @Test
    public void delEmp() throws IOException {
        // 获取的SqlSession不会自定提交
        SqlSession sqlSession = getsqlSessionFactory().openSession();

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

            int i = mapper.deleteEmpById(2);
            System.out.println("=============" + i);
            // 手动提交
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

    /**
     * 测试多个参数的传递,参数是集合
     * @throws IOException
     */
    @Test
    public void test1() throws IOException {
        // 获取的SqlSession不会自定提交
        SqlSession sqlSession = getsqlSessionFactory().openSession();

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            ArrayList<Integer> arrayList = new ArrayList<>();
            arrayList.add(1);
            arrayList.add(5);
            List<Employee> empByList = mapper.getEmpByList(arrayList);
            System.out.println(empByList);
            // 手动提交
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }

    /**
     * 测试多个参数的传递,参数是数组
     * @throws IOException
     */
    @Test
    public void test2() throws IOException {
        // 获取的SqlSession不会自定提交
        SqlSession sqlSession = getsqlSessionFactory().openSession();

        try {
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
            Integer[] integers = {1 ,5};
            List<Employee> empByList = mapper.getEmpByArray(integers);
            System.out.println(empByList);
            // 手动提交
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }


}
